package com.rxh.util;


import java.io.Serializable;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.ui.ModelMap;

import com.rxh.pojo.finance.FinanceBusinessReconMoney;
import com.rxh.pojo.finance.FinanceDrawing;
import com.rxh.vo.VoCoreOrder;
import com.rxh.vo.VoCoreOrderChange;

/**
 * 
 * @author xuzm
 * @date 20180411
 *
 */
public class BusinessReportExcelUtils implements Serializable {


	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	
	
	@SuppressWarnings("unchecked")
	public void downloadReconData(FinanceBusinessReconMoney businessReconMoney,Map<String,Object> resultMap,HttpServletRequest request, HttpServletResponse response){
		
		   HSSFWorkbook workbook = new HSSFWorkbook();
		   List<FinanceBusinessReconMoney> businessReconMoneyList = (List<FinanceBusinessReconMoney>)resultMap.get("businessReconMoneyList");
		   List<VoCoreOrder> settlePlusOrderList = (List<VoCoreOrder>)resultMap.get("settlePlusOrderList");
	       List<VoCoreOrder> noSettlePlusOrderList = (List<VoCoreOrder>)resultMap.get("noSettlePlusOrderList");
	       List<VoCoreOrder> deductPlusOrderList = (List<VoCoreOrder>)resultMap.get("deductPlusOrderList");
	       List<VoCoreOrder> bondPlusOrderList = (List<VoCoreOrder>)resultMap.get("bondPlusOrderList");
	       List<VoCoreOrder> noBondPlusOrderList = (List<VoCoreOrder>)resultMap.get("noBondPlusOrderList");
	       List<VoCoreOrderChange> orderChangeOrderList = (List<VoCoreOrderChange>)resultMap.get("orderChangeOrderList");
	       List<FinanceDrawing> drawingList = (List<FinanceDrawing>)resultMap.get("drawingList");
	       
		   createBusinessReconMoneySheet(workbook,businessReconMoneyList);
		   workbook = createOrderListSheet(workbook,settlePlusOrderList,"清算金额");
		   workbook = createOrderListSheet(workbook,noSettlePlusOrderList,"未清算金额");
		   workbook = createDeductOrderListSheet(workbook,deductPlusOrderList,"交易手续费详情");
		   workbook = createBondOrderListSheet(workbook,bondPlusOrderList,"释放保证金");
		   workbook = createBondOrderListSheet(workbook,noBondPlusOrderList,"新增保证金");
		   workbook = createOrderChangeListSheet(workbook,orderChangeOrderList,"退款拒付详情");
		   workbook = createDrawingListSheet(workbook,drawingList,"提现记录");
		   
		   if(businessReconMoney.getReconType() == 1){
	    		
	        	request.setAttribute("reconDate",businessReconMoney.getReconDate());

	    	}else{
	    		
	    		SimpleDateFormat reconDateFmt = new SimpleDateFormat("yyyy-MM");
	    		
	        	try {
	        		
					request.setAttribute("reconDate", reconDateFmt.parse(businessReconMoney.getReconDate()));
					
				} catch (ParseException e) {
					
					e.printStackTrace();
					
				}
	    		
	    	}
	    	request.setAttribute("reconType", businessReconMoney.getReconType());

	 	    try {
	 	    	
	 	    	new ExcelExportByPoiUtils().buildExcelDocument(new ModelMap(), workbook, request, response);
	 			
	 		} catch (Exception e) {
	 			
	 			e.printStackTrace();
	 		}
			
		
	 }
	
	
	public HSSFWorkbook createDrawingListSheet(HSSFWorkbook workbook,List<FinanceDrawing> drawingList,String sheetName){
		  
		  HSSFSheet sheet = workbook.createSheet(sheetName);  
		  
		  HSSFRow row = sheet.createRow((int) 0); 
		  
		  HSSFCellStyle style = workbook.createCellStyle();  
	        
	      style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
	      style.setFillForegroundColor(HSSFColor.BRIGHT_GREEN.index);
	      style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	      
	        row = sheet.createRow((int) 0);  
	        HSSFCell cell = row.createCell(0);   
	        cell.setCellValue("商户号");   
	        cell.setCellStyle(style); 

	        cell.setCellStyle(style);   
	        cell = row.createCell(1);   
	        cell.setCellValue("商户名称");   

	        cell.setCellStyle(style);   
	        cell = row.createCell(2);   
	        cell.setCellValue("币种");  
	        
	        cell.setCellStyle(style);   
	        cell = row.createCell(3);   
	        cell.setCellValue("提现金额");   

	        cell.setCellStyle(style);   
	        cell = row.createCell(4);   
	        cell.setCellValue("划款金额"); 
	        
	        cell.setCellStyle(style); 
	        cell = row.createCell(5); 
	        cell.setCellValue("实际划款金额"); 
	        
	        cell.setCellStyle(style); 
	        cell = row.createCell(6);   
	        cell.setCellValue("提现汇率"); 
	        
	        cell.setCellStyle(style); 
	        cell = row.createCell(7);   
	        cell.setCellValue("申请人"); 
	        
	        cell.setCellStyle(style); 
	        cell = row.createCell(8);   
	        cell.setCellValue("申请时间"); 

	        cell.setCellStyle(style); 
	        cell = row.createCell(9);   
	        cell.setCellValue("审核人");   

	        cell.setCellStyle(style);    	        
	        cell = row.createCell(10);   
	        cell.setCellValue("审核时间");   

	        cell.setCellStyle(style); 
	        cell = row.createCell(11);   
	        cell.setCellValue("划款人"); 
	        
	        cell.setCellStyle(style); 
	        cell = row.createCell(12);   
	        cell.setCellValue("划款时间"); 
	         
	        cell.setCellStyle(style); 
	        cell = row.createCell(13);   
	        cell.setCellValue("状态");
	        cell.setCellStyle(style);
	        
          sheet.setColumnWidth(0,20 * 256);
          sheet.setColumnWidth(1,20 * 256);
          sheet.setColumnWidth(2,20 * 256);
          sheet.setColumnWidth(3,20 * 256);
          sheet.setColumnWidth(4,20 * 256);
          sheet.setColumnWidth(5,20 * 256);
          sheet.setColumnWidth(6,20 * 256);
          sheet.setColumnWidth(7,20 * 256);
          sheet.setColumnWidth(8,20 * 256);
          sheet.setColumnWidth(9,20 * 256);
          sheet.setColumnWidth(10,20 * 256);
          sheet.setColumnWidth(11,20 * 256);
          sheet.setColumnWidth(12,20 * 256);
          sheet.setColumnWidth(13,20 * 256);

          if(null != drawingList){
        	  
        	  for(int i = 0; i < drawingList.size() ; i++){
  	        	
  	        	FinanceDrawing drawing = drawingList.get(i);
  	        	
  	        	row = sheet.createRow((int) i + 1); 
  	        	
  	        	row.createCell(0).setCellValue(drawing.getRefId() + ""); 
  	        	row.createCell(1).setCellValue(drawing.getMerName()); 
  	        	row.createCell(2).setCellValue(drawing.getCurrency()); 
  	        	row.createCell(3).setCellValue(drawing.getAmount() + ""); 
  	        	row.createCell(4).setCellValue(drawing.getTransferAmount() + ""); 
  	        	row.createCell(5).setCellValue(drawing.getRealAmount() + ""); 
  	        	row.createCell(6).setCellValue(drawing.getRate() + ""); 
  	        	row.createCell(7).setCellValue(drawing.getApplicant()); 
  	        	if(null != drawing.getApplicationTime()){
  	        		
  		        	row.createCell(8).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(drawing.getApplicationTime())); 
  	        		
  	        	}else{
  	        		
  	        		row.createCell(8).setCellValue("无"); 
  	        		
  	        	}
  	        	if(null != drawing.getAuditTime()){
  	        		
  	        		row.createCell(10).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(drawing.getAuditTime())); 
  	        		
  	        	}else{
  	        		
  	        		row.createCell(10).setCellValue("无");
  	        	}
  	        	
  	        	row.createCell(9).setCellValue(drawing.getAuditor()); 
  	        	row.createCell(11).setCellValue(drawing.getTransfer()); 
  	        	if(null != drawing.getTransferTime()){
  	        		
  		        	row.createCell(12).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(drawing.getTransferTime())); 

  	        	}else{
  	        		
  	        		row.createCell(12).setCellValue("无"); 
  	        		
  	        	}
  	        	if(drawing.getStatus() == 1){
  	        		
  	        		row.createCell(13).setCellValue("待审核"); 
  	        		
  	        	}else if(drawing.getStatus() == 5){
  	        		
  	        		row.createCell(13).setCellValue("待复核"); 
  	        		
  	        	}else if(drawing.getStatus() == 2){
  	        		
  	        		row.createCell(13).setCellValue("待划款"); 
  	        		
  	        	}else if(drawing.getStatus() == 3 || drawing.getStatus() == 4){
  	        		
  	        		row.createCell(13).setCellValue("划款成功"); 
  	        		
  	        	}
  	        	
  	        	
  	        }
        	  
        	  
          }
	        
		  return workbook;
		  
	}
	
	public HSSFWorkbook createOrderChangeListSheet(HSSFWorkbook workbook,List<VoCoreOrderChange> orderOrderChangeList,String sheetName){
		  
		  HSSFSheet sheet = workbook.createSheet(sheetName);  
		  
		  HSSFRow row = sheet.createRow((int) 0); 
		  
		  HSSFCellStyle style = workbook.createCellStyle();  
	        
	      style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
	      style.setFillForegroundColor(HSSFColor.BRIGHT_GREEN.index);
	      style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	      
	        row = sheet.createRow((int) 0);  
	        HSSFCell cell = row.createCell(0);   
	        cell.setCellValue("平台订单号");   
	        cell.setCellStyle(style); 

	        cell.setCellStyle(style);   
	        cell = row.createCell(1);   
	        cell.setCellValue("商户号");   

	        cell.setCellStyle(style);   
	        cell = row.createCell(2);   
	        cell.setCellValue("域名");   

	        cell.setCellStyle(style);   
	        cell = row.createCell(3);   
	        cell.setCellValue("商户订单号"); 
	        
	        cell.setCellStyle(style); 
	        cell = row.createCell(4); 
	        cell.setCellValue("币种"); 
	        
	        cell.setCellStyle(style); 
	        cell = row.createCell(5);   
	        cell.setCellValue("订单金额"); 
	        
	        cell.setCellStyle(style); 
	        cell = row.createCell(6);   
	        cell.setCellValue("订单人民币金额"); 
	        
	        cell.setCellStyle(style); 
	        cell = row.createCell(7);   
	        cell.setCellValue("交易时间"); 

	        cell.setCellStyle(style); 
	        cell = row.createCell(8);   
	        cell.setCellValue("退款拒付金额");   

	        cell.setCellStyle(style);    	        
	        cell = row.createCell(9);   
	        cell.setCellValue("退款拒付人民币金额");   

	        cell.setCellStyle(style); 
	        cell = row.createCell(10);   
	        cell.setCellValue("退款拒付处理费金额"); 
	        
	        cell.setCellStyle(style); 
	        cell = row.createCell(11);   
	        cell.setCellValue("退款拒付人民币处理费金额"); 
	        
	        cell.setCellStyle(style); 
	        cell = row.createCell(12);   
	        cell.setCellValue("归还手续费金额"); 
	         
	        cell.setCellStyle(style); 
	        cell = row.createCell(13);   
	        cell.setCellValue("退款拒付状态"); 
	        
	        cell.setCellStyle(style); 
	        cell = row.createCell(14);   
	        cell.setCellValue("退款拒付申请时间"); 
	        
	        cell.setCellStyle(style); 
	        cell = row.createCell(15);   
	        cell.setCellValue("退款拒付审核时间"); 
	        cell.setCellStyle(style); 
	        
	        sheet.setColumnWidth(0, 20 * 256); 
	        sheet.setColumnWidth(1, 20 * 256); 
	        sheet.setColumnWidth(2, 20 * 256); 
	        sheet.setColumnWidth(3, 20 * 256); 
	        sheet.setColumnWidth(4, 20 * 256); 
	        sheet.setColumnWidth(5, 20 * 256); 
	        sheet.setColumnWidth(6, 20 * 256); 
	        sheet.setColumnWidth(7, 20 * 256); 
	        sheet.setColumnWidth(8, 20 * 256); 
	        sheet.setColumnWidth(9, 20 * 256); 
	        sheet.setColumnWidth(10, 20 * 256); 
	        sheet.setColumnWidth(11, 30 * 256); 
	        sheet.setColumnWidth(12, 20 * 256); 
	        sheet.setColumnWidth(13, 20 * 256); 
	        sheet.setColumnWidth(14, 20 * 256); 
	        sheet.setColumnWidth(15, 20 * 256); 
	        
	        if(null != orderOrderChangeList){
	        	
	        	for(int i = 0; i < orderOrderChangeList.size() ; i++){
		        	
		        	VoCoreOrderChange orderchange = orderOrderChangeList.get(i);
		        	
		        	row = sheet.createRow((int) i + 1); 
		        	
		        	row.createCell(0).setCellValue(orderchange.getId() + ""); 
		        	row.createCell(1).setCellValue(orderchange.getMerId() + "(" + orderchange.getMerName() + ")"); 
		        	row.createCell(2).setCellValue(orderchange.getSiteUrl()); 
		        	row.createCell(3).setCellValue(orderchange.getMerOrderId()); 
		        	row.createCell(4).setCellValue(orderchange.getCurrency()); 
		        	row.createCell(5).setCellValue(orderchange.getAmount() + ""); 
		        	row.createCell(6).setCellValue(orderchange.getRmbAmount() + ""); 
		        	row.createCell(7).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(orderchange.getTradeTime())); 
		        	row.createCell(8).setCellValue(orderchange.getAmount() + ""); 
		        	row.createCell(9).setCellValue(orderchange.getRmbAmount() + ""); 
		        	row.createCell(10).setCellValue(orderchange.getFee() + ""); 
		        	row.createCell(11).setCellValue(orderchange.getRmbFee() + ""); 
		        	row.createCell(12).setCellValue(orderchange.getReturnFee() + ""); 
		        	if(orderchange.getType() == 2 || orderchange.getType() == 3){
		        		
		        		row.createCell(13).setCellValue("退款"); 
		        		
		        	}else{
		        		
		        		row.createCell(13).setCellValue("拒付"); 
		        		
		        	}
		        	
		        	row.createCell(14).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(orderchange.getApplicationTime())); 
		        	row.createCell(15).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(orderchange.getAuditTime())); 
		        	
		        	
		        }
	        	
	        	
	        }
	        

		
		  return workbook;
		  
	}
	
	public HSSFWorkbook createBondOrderListSheet(HSSFWorkbook workbook,List<VoCoreOrder> orderList,String sheetName){
		  
		  HSSFSheet sheet = workbook.createSheet(sheetName);  
		  
		  HSSFRow row = sheet.createRow((int) 0); 
		  
		  HSSFCellStyle style = workbook.createCellStyle();  
	        
	      style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
	      style.setFillForegroundColor(HSSFColor.BRIGHT_GREEN.index);
	      style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	      
	        row = sheet.createRow((int) 0);  
	        HSSFCell cell = row.createCell(0);   
	        cell.setCellValue("平台订单号");   
	        cell.setCellStyle(style); 

	        cell.setCellStyle(style);   
	        cell = row.createCell(1);   
	        cell.setCellValue("商户号");   

	        cell.setCellStyle(style);   
	        cell = row.createCell(2);   
	        cell.setCellValue("域名");   

	        cell.setCellStyle(style);   
	        cell = row.createCell(3);   
	        cell.setCellValue("商户订单号"); 
	        
	        cell.setCellStyle(style); 
	        cell = row.createCell(4); 
	        cell.setCellValue("币种"); 
	        
	        cell.setCellStyle(style); 
	        cell = row.createCell(5);   
	        cell.setCellValue("订单金额"); 
	        
	        cell.setCellStyle(style); 
	        cell = row.createCell(6);   
	        cell.setCellValue("订单人民币金额"); 
	        
	        cell.setCellStyle(style); 
	        cell = row.createCell(7);   
	        cell.setCellValue("交易时间"); 

	        cell.setCellStyle(style); 
	        cell = row.createCell(8);   
	        cell.setCellValue("保证金释放金额");   

	        cell.setCellStyle(style);    	        
	        cell = row.createCell(9);   
	        cell.setCellValue("保证金释放人民币金额");   

	        cell.setCellStyle(style); 
	        cell = row.createCell(10);   
	        cell.setCellValue("保证金释放时间"); 
	         
	        cell.setCellStyle(style); 
	        cell = row.createCell(11);   
	        cell.setCellValue("保证金释放状态");
	        cell.setCellStyle(style);
	        
	        sheet.setColumnWidth(0, 20 * 256);
	        sheet.setColumnWidth(1, 20 * 256);
	        sheet.setColumnWidth(2, 20 * 256);
	        sheet.setColumnWidth(3, 20 * 256);
	        sheet.setColumnWidth(4, 20 * 256);
	        sheet.setColumnWidth(5, 20 * 256);
	        sheet.setColumnWidth(6, 20 * 256);
	        sheet.setColumnWidth(7, 20 * 256);
	        sheet.setColumnWidth(8, 20 * 256);
	        sheet.setColumnWidth(9, 20 * 256);
	        sheet.setColumnWidth(10, 20 * 256);
	        sheet.setColumnWidth(11, 20 * 256);
	        
	        if(null != orderList){
	        	
	        	for(int i = 0; i < orderList.size() ; i++){
		        	
		        	VoCoreOrder order = orderList.get(i);
		        	
		        	row = sheet.createRow((int) i + 1); 
		        	
		        	row.createCell(0).setCellValue(order.getId() + ""); 
		        	row.createCell(1).setCellValue(order.getMerId() + "(" + order.getMerName() + ")"); 
		        	row.createCell(2).setCellValue(order.getSiteUrl()); 
		        	row.createCell(3).setCellValue(order.getMerOrderId()); 
		        	row.createCell(4).setCellValue(order.getCurrency()); 
		        	row.createCell(5).setCellValue(order.getAmount() + ""); 
		        	row.createCell(6).setCellValue(order.getRmbAmount() + ""); 
		        	row.createCell(7).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(order.getTradeTime())); 
		        	row.createCell(8).setCellValue(order.getBondAmount() + ""); 
		        	row.createCell(9).setCellValue(order.getBondRmbAmount() + ""); 
		        	if(null != order.getBondTime()){
		        		row.createCell(10).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(order.getBondTime())); 
		        	}else{
		        		row.createCell(10).setCellValue("无"); 
		        	}
		        	
		        	if(order.getBondStatus() == 1){
		        		
		        		row.createCell(11).setCellValue("未释放"); 
		        		
		        	}else{
		        		
		        		row.createCell(11).setCellValue("释放"); 
		        		
		        	}
		        	
		        	
		        }
	        	
	        	
	        }
	        
		    return workbook;
		  
	}
	
	public HSSFWorkbook createDeductOrderListSheet(HSSFWorkbook workbook,List<VoCoreOrder> orderList,String sheetName){
		  
		  HSSFSheet sheet = workbook.createSheet(sheetName);  
		  
		  HSSFRow row = sheet.createRow((int) 0); 
		  
		  HSSFCellStyle style = workbook.createCellStyle();  
	        
	      style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
	      style.setFillForegroundColor(HSSFColor.BRIGHT_GREEN.index);
	      style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	      
	        row = sheet.createRow((int) 0);  
	        HSSFCell cell = row.createCell(0);   
	        cell.setCellValue("平台订单号");   
	        cell.setCellStyle(style); 

	        cell.setCellStyle(style);   
	        cell = row.createCell(1);   
	        cell.setCellValue("商户号");   

	        cell.setCellStyle(style);   
	        cell = row.createCell(2);   
	        cell.setCellValue("域名");   

	        cell.setCellStyle(style);   
	        cell = row.createCell(3);   
	        cell.setCellValue("商户订单号"); 
	        
	        cell.setCellStyle(style); 
	        cell = row.createCell(4); 
	        cell.setCellValue("币种"); 
	        
	        cell.setCellStyle(style); 
	        cell = row.createCell(5);   
	        cell.setCellValue("订单金额"); 
	        
	        cell.setCellStyle(style); 
	        cell = row.createCell(6);   
	        cell.setCellValue("订单人民币金额"); 
	        
	        cell.setCellStyle(style); 
	        cell = row.createCell(7);   
	        cell.setCellValue("交易时间"); 

	        cell.setCellStyle(style); 
	        cell = row.createCell(8);   
	        cell.setCellValue("手续费金额");   

	        cell.setCellStyle(style);    	        
	        cell = row.createCell(9);   
	        cell.setCellValue("手续费人民币金额");  
	        cell.setCellStyle(style); 
	        
	        sheet.setColumnWidth(0, 20 * 256);
	        sheet.setColumnWidth(1, 20 * 256);
	        sheet.setColumnWidth(2, 20 * 256);
	        sheet.setColumnWidth(3, 20 * 256);
	        sheet.setColumnWidth(4, 20 * 256);
	        sheet.setColumnWidth(5, 20 * 256);
	        sheet.setColumnWidth(6, 20 * 256);
	        sheet.setColumnWidth(7, 20 * 256);
	        sheet.setColumnWidth(8, 20 * 256);
	        sheet.setColumnWidth(9, 20 * 256);
	        
	        if(null != orderList){
	        	
	        	for(int i = 0; i < orderList.size() ; i++){
		        	
		        	VoCoreOrder order = orderList.get(i);
		        	
		        	row = sheet.createRow((int) i + 1); 
		        	
		        	row.createCell(0).setCellValue(order.getId() + ""); 
		        	row.createCell(1).setCellValue(order.getMerId() + "(" + order.getMerName() + ")"); 
		        	row.createCell(2).setCellValue(order.getSiteUrl()); 
		        	row.createCell(3).setCellValue(order.getMerOrderId()); 
		        	row.createCell(4).setCellValue(order.getCurrency()); 
		        	row.createCell(5).setCellValue(order.getAmount() + ""); 
		        	row.createCell(6).setCellValue(order.getRmbAmount() + ""); 
		        	row.createCell(7).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(order.getTradeTime())); 
		        	row.createCell(8).setCellValue(order.getDeduct() + ""); 
		        	row.createCell(9).setCellValue(order.getRmbDeduct() + ""); 
		
		        }
	        	
	        }
	        
		    return workbook;
	}
	
	public HSSFWorkbook createOrderListSheet(HSSFWorkbook workbook,List<VoCoreOrder> orderList,String sheetName){
		  
		  HSSFSheet sheet = workbook.createSheet(sheetName);  
		  
		  HSSFRow row = sheet.createRow((int) 0); 
		  
		  HSSFCellStyle style = workbook.createCellStyle();  
	        
	      style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
	      style.setFillForegroundColor(HSSFColor.BRIGHT_GREEN.index);
	      style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	      
	        row = sheet.createRow((int) 0);  
	        HSSFCell cell = row.createCell(0);   
	        cell.setCellValue("平台订单号");   
	        cell.setCellStyle(style); 

	        cell.setCellStyle(style);   
	        cell = row.createCell(1);   
	        cell.setCellValue("商户号");   

	        cell.setCellStyle(style);   
	        cell = row.createCell(2);   
	        cell.setCellValue("域名");   

	        cell.setCellStyle(style);   
	        cell = row.createCell(3);   
	        cell.setCellValue("商户订单号"); 
	        
	        cell.setCellStyle(style); 
	        cell = row.createCell(4); 
	        cell.setCellValue("币种"); 
	        
	        cell.setCellStyle(style); 
	        cell = row.createCell(5);   
	        cell.setCellValue("订单金额"); 
	        
	        cell.setCellStyle(style); 
	        cell = row.createCell(6);   
	        cell.setCellValue("订单人民币金额"); 
	        
	        cell.setCellStyle(style); 
	        cell = row.createCell(7);   
	        cell.setCellValue("交易时间"); 

	        cell.setCellStyle(style); 
	        cell = row.createCell(8);   
	        cell.setCellValue("清算金额");   

	        cell.setCellStyle(style);    	        
	        cell = row.createCell(9);   
	        cell.setCellValue("清算人民币金额");   

	        cell.setCellStyle(style); 
	        cell = row.createCell(10);   
	        cell.setCellValue("清算时间"); 
	         
	        cell.setCellStyle(style); 
	        cell = row.createCell(11);   
	        cell.setCellValue("清算状态"); 
	        cell.setCellStyle(style); 
	        
	        sheet.setColumnWidth(0, 20 * 256);
	        sheet.setColumnWidth(1, 20 * 256);
	        sheet.setColumnWidth(2, 20 * 256);
	        sheet.setColumnWidth(3, 20 * 256);
	        sheet.setColumnWidth(4, 20 * 256);
	        sheet.setColumnWidth(5, 20 * 256);
	        sheet.setColumnWidth(6, 20 * 256);
	        sheet.setColumnWidth(7, 20 * 256);
	        sheet.setColumnWidth(8, 20 * 256);
	        sheet.setColumnWidth(9, 20 * 256);
	        sheet.setColumnWidth(10, 20 * 256);
	        sheet.setColumnWidth(11, 20 * 256);
	        
	        if(null != orderList){
	        	
	        	for(int i = 0; i < orderList.size() ; i++){
		        	
		        	VoCoreOrder order = orderList.get(i);
		        	
		        	row = sheet.createRow((int) i + 1); 
		        	
		        	row.createCell(0).setCellValue(order.getId() + ""); 
		        	row.createCell(1).setCellValue(order.getMerId() + "(" + order.getMerName() + ")"); 
		        	row.createCell(2).setCellValue(order.getSiteUrl()); 
		        	row.createCell(3).setCellValue(order.getMerOrderId()); 
		        	row.createCell(4).setCellValue(order.getCurrency()); 
		        	row.createCell(5).setCellValue(order.getAmount() + ""); 
		        	row.createCell(6).setCellValue(order.getRmbAmount() + ""); 
		        	row.createCell(7).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(order.getTradeTime())); 
		        	row.createCell(8).setCellValue(order.getSettleAmount() + ""); 
		        	row.createCell(9).setCellValue(order.getSettleRmbAmount() + ""); 
		        	if(null != order.getFinanceTime()){
		        		
		        		row.createCell(10).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(order.getFinanceTime()));
		        		
		        	}else{
		        		
			        	row.createCell(10).setCellValue("无"); 
		        	}
		        	if(order.getFinanceStatus() == 1){
		        		
		        		row.createCell(11).setCellValue("清算");
		        		
		        	}else{
		        		
		        		row.createCell(11).setCellValue("未清算");
		        		
		        	}
		        	
		        }
	        	
	        	
	        }
	        
		    return workbook;
	}
	
	public HSSFWorkbook createBusinessReconMoneySheet(HSSFWorkbook workbook,List<FinanceBusinessReconMoney> businessReconMoneyList){
		
		   FinanceBusinessReconMoney businessReconMoney = businessReconMoneyList.get(0);
		   HSSFSheet sheet = workbook.createSheet("商户余额记录");  
	       sheet.addMergedRegion(new CellRangeAddress(0,0,0,2));
	       sheet.addMergedRegion(new CellRangeAddress(0,0,4,6));
	       sheet.addMergedRegion(new CellRangeAddress(0,0,8,10));
	       HSSFRow row = sheet.createRow((int) 0); 
	         
	        HSSFCellStyle style = workbook.createCellStyle();  
	        
	        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
	        style.setFillForegroundColor(HSSFColor.BRIGHT_GREEN.index);
	        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

	        HSSFCell cell = row.createCell(0);
	        cell.setCellValue("可用资金");
	        cell.setCellStyle(style); 
	        
	        style = workbook.createCellStyle();
	        style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); 
	        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
	        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	        cell = row.createCell(4);   
	        cell.setCellValue("保证金");   
	        cell.setCellStyle(style); 
	        
	        style = workbook.createCellStyle();
	        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
	        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
	        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	        cell = row.createCell(8);   
	        cell.setCellValue("未清算金额");
	        cell.setCellStyle(style); 
	        
	        //第二行
	        row = sheet.createRow((int) 1);
	        //可用余额
	        cell = row.createCell(0);   
	        cell.setCellValue("期初余额");  
	        cell = row.createCell(1);   
	        cell.setCellValue(businessReconMoney.getPrimitiveUsableAmount() + ""); 
	        cell = row.createCell(2);   
	        cell.setCellValue("0"); 
	        
	        //保证金 	        
	        cell = row.createCell(4);   
	        cell.setCellValue("期初余额");  
	        cell = row.createCell(5);   
	        cell.setCellValue(businessReconMoney.getPrimitiveBondAmount() + ""); 
	        cell = row.createCell(6);   
	        cell.setCellValue("0"); 
	        
	        //未清算金额
	        cell = row.createCell(8);   
	        cell.setCellValue("期初余额");  
	        cell = row.createCell(9);   
	        cell.setCellValue(businessReconMoney.getPrimitiveNoSettleAmount()+ ""); 
	        cell = row.createCell(10);   
	        cell.setCellValue("0"); 
	        
	        //第三行
	        row = sheet.createRow((int) 2);
	        //可用余额
	        cell = row.createCell(0);   
	        cell.setCellValue("清算金额");  
	        cell = row.createCell(1);   
	        cell.setCellValue(businessReconMoney.getSettlePlusAmount() + ""); 
	        cell = row.createCell(2);   
	        cell.setCellValue("0"); 
	        
	        //保证金 	        
	        cell = row.createCell(4);   
	        cell.setCellValue("增加保证金");  
	        cell = row.createCell(5);   
	        cell.setCellValue(businessReconMoney.getBondNoReleaseAmount() + ""); 
	        cell = row.createCell(6);   
	        cell.setCellValue("0"); 
	        
	        //未清算金额
	        cell = row.createCell(8);   
	        cell.setCellValue("新增交易总额");  
	        cell = row.createCell(9);   
	        cell.setCellValue(businessReconMoney.getNoSettlePlusAmount().add(businessReconMoney.getBondNoReleaseAmount()).add(businessReconMoney.getDeductPlusAmount()) + ""); 
	        cell = row.createCell(10);   
	        cell.setCellValue("0"); 
	        
	        
	        //第四行
	        row = sheet.createRow((int) 3);
	        //可用余额
//	        cell = row.createCell(0);   
//	        cell.setCellValue("保证金调整");  
//	        cell = row.createCell(1);   
//	        cell.setCellValue(businessReconMoney.getBondReleaseAmount() + ""); 
//	        cell = row.createCell(2);   
//	        cell.setCellValue((businessReconMoney.getBondNoReleaseAmount().compareTo(new BigDecimal(0)) == -1? new BigDecimal(0):new BigDecimal(0).subtract(businessReconMoney.getBondNoReleaseAmount())) + ""); 
//	        
	        
	        //可用余额
	        cell = row.createCell(0);   
	        cell.setCellValue("退款金额");  
	        cell = row.createCell(1);   
	        cell.setCellValue("0"); 
	        cell = row.createCell(2);   
	        cell.setCellValue((businessReconMoney.getRefundAmount().compareTo(new BigDecimal(0)) == -1? new BigDecimal(0):new BigDecimal(0).subtract(businessReconMoney.getRefundAmount())) + ""); 
	        
	        
	        //保证金 	        
	        cell = row.createCell(4);   
	        cell.setCellValue("释放保证金");  
	        cell = row.createCell(5);   
	        cell.setCellValue(businessReconMoney.getBondReleaseAmount() + ""); 
	        cell = row.createCell(6);   
	        cell.setCellValue(("0")); 
	        
	        //手续费金额
	        cell = row.createCell(8);   
	        cell.setCellValue("手续费金额");  
	        cell = row.createCell(9);   
	        cell.setCellValue("0"); 
	        cell = row.createCell(10);   
	        cell.setCellValue((businessReconMoney.getDeductPlusAmount().compareTo(new BigDecimal(0)) == -1? new BigDecimal(0):new BigDecimal(0).subtract(businessReconMoney.getDeductPlusAmount())) + "");
	      

	        //第五行
	        row = sheet.createRow((int) 4);

	        //可用余额
	        cell = row.createCell(0);   
	        cell.setCellValue("拒付金额");  
	        cell = row.createCell(1);   
	        cell.setCellValue("0"); 
	        cell = row.createCell(2);   
	        cell.setCellValue((businessReconMoney.getProtestAmount().compareTo(new BigDecimal(0)) == -1? new BigDecimal(0):new BigDecimal(0).subtract(businessReconMoney.getProtestAmount())) + ""); 
	        
	        //保证金 	        
	        cell = row.createCell(4);   
	        cell.setCellValue("其他金额");  
	        cell = row.createCell(5);   
	        cell.setCellValue("0"); 
	        cell = row.createCell(6);   
	        cell.setCellValue("0"); 
	        

	        //保证金
	        cell = row.createCell(8);   
	        cell.setCellValue("保证金");  
	        cell = row.createCell(9);   
	        cell.setCellValue("0"); 
	        cell = row.createCell(10);   
	        cell.setCellValue((businessReconMoney.getBondNoReleaseAmount().compareTo(new BigDecimal(0)) == -1? new BigDecimal(0):new BigDecimal(0).subtract(businessReconMoney.getBondNoReleaseAmount())) + "");
	        
	        
	        //第六行
	        row = sheet.createRow((int) 5);
	        
	        //可用余额
	        cell = row.createCell(0);   
	        cell.setCellValue("拒付处理费金额");  
	        cell = row.createCell(1);   
	        cell.setCellValue("0"); 
	        cell = row.createCell(2);   
	        cell.setCellValue((businessReconMoney.getProtestChargeAmount().compareTo(new BigDecimal(0)) == -1? new BigDecimal(0):new BigDecimal(0).subtract(businessReconMoney.getProtestChargeAmount())) + ""); 
	        
	        //未清算金额
	        cell = row.createCell(8);   
	        cell.setCellValue("清算金额");  
	        cell = row.createCell(9);   
	        cell.setCellValue("0"); 
	        cell = row.createCell(10);   
	        cell.setCellValue((businessReconMoney.getSettlePlusAmount().compareTo(new BigDecimal(0)) == -1? new BigDecimal(0):new BigDecimal(0).subtract(businessReconMoney.getSettlePlusAmount())) + "");
	        
	        //第七行
	        row = sheet.createRow((int) 6);
	        //可用余额
	        cell = row.createCell(0);   
	        cell.setCellValue("退款手续费");  
	        cell = row.createCell(1);   
	        cell.setCellValue("0"); 
	        cell = row.createCell(2);   
	        cell.setCellValue((businessReconMoney.getRefundChargeAmount().compareTo(new BigDecimal(0)) == -1? new BigDecimal(0):new BigDecimal(0).subtract(businessReconMoney.getRefundChargeAmount())) + ""); 

	        //保证金 	        
	        cell = row.createCell(4);   
	        cell.setCellValue("期末余额");  
	        cell = row.createCell(5);   
	        cell.setCellValue(businessReconMoney.getFinalBondAmount() + ""); 
	        
	        //其他金额
	        cell = row.createCell(8);   
	        cell.setCellValue("其他金额");  
	        cell = row.createCell(9);   
	        cell.setCellValue("0"); 
	        cell = row.createCell(10);   
	        cell.setCellValue("0"); 
	        
	        //第八行
	        row = sheet.createRow((int) 7);
	        //可用余额
	        cell = row.createCell(0);   
	        cell.setCellValue("退款返回交易手续费");  
	        cell = row.createCell(1);   
	        cell.setCellValue(businessReconMoney.getReturnDeductAmount() + ""); 
	        cell = row.createCell(2);   
	        cell.setCellValue("0"); 
	        
	        //第九行
	        row = sheet.createRow((int) 8);
	        
	        //可用余额
	        cell = row.createCell(0);   
	        cell.setCellValue("已提待审核金额");  
	        cell = row.createCell(1);   
	        cell.setCellValue("0"); 
	        cell = row.createCell(2);   
	        cell.setCellValue((businessReconMoney.getDrawingNoAuditAmount().compareTo(new BigDecimal(0)) == -1? new BigDecimal(0):new BigDecimal(0).subtract(businessReconMoney.getDrawingNoAuditAmount())) + ""); 
	        
	        //未清算金额
	        cell = row.createCell(8);   
	        cell.setCellValue("期末余额");  
	        cell = row.createCell(9);   
	        cell.setCellValue(businessReconMoney.getFinalNoSettleAmount() + ""); 
	        
	        //第十行
	        row = sheet.createRow((int) 9);
	        //可用余额
	        cell = row.createCell(0);   
	        cell.setCellValue("已提金额");  
	        cell = row.createCell(1);   
	        cell.setCellValue("0"); 
	        cell = row.createCell(2);   
	        cell.setCellValue((businessReconMoney.getDrawingAuditAmount().compareTo(new BigDecimal(0)) == -1? new BigDecimal(0):new BigDecimal(0).subtract(businessReconMoney.getDrawingAuditAmount())) + ""); 
	        
	        //第十一行
	        row = sheet.createRow((int) 10);
	        //可用余额
	        cell = row.createCell(0);   
	        cell.setCellValue("其他费用");  
	        cell = row.createCell(1);   
	        cell.setCellValue("0"); 
	        cell = row.createCell(2);   
	        cell.setCellValue("0"); 
	        
	        
	        //第十三行
	        row = sheet.createRow((int) 12);
	        //可用余额
	        cell = row.createCell(0);   
	        cell.setCellValue("期末余额");  
	        cell = row.createCell(1);   
	        cell.setCellValue(businessReconMoney.getFinalUsableAmount() + ""); 

	        
	        sheet.setColumnWidth(0, 20 * 256); 
	        sheet.setColumnWidth(1, 20 * 256); 
	        sheet.setColumnWidth(2, 20 * 256);
	        sheet.setColumnWidth(3, 20 * 256);
	        sheet.setColumnWidth(4, 20 * 256); 
	        sheet.setColumnWidth(5, 20 * 256);
	        sheet.setColumnWidth(6, 20 * 256);
	        sheet.setColumnWidth(7, 20 * 256);
	        sheet.setColumnWidth(8, 20 * 256);
	        sheet.setColumnWidth(9, 20 *256);
	        sheet.setColumnWidth(10, 20 *256);	        

		    return workbook;
		
		
	}
	
	
}
